package cm.tl.pms.util.fileParser.excel;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


@Slf4j
public class ExcelParserUtil {


    @Data
    public static class ExcelParseConfig {
        // 第一级分组规则，用于隔离不同公司的表头组合
        private List<String> firstLevelGroupHeaders = new ArrayList<>();
        // 需要进行分组求和的表头和对应的分组表头
        private List<GroupSumRule> groupSumRules = new ArrayList<>();
        // 需要进行去重的表头
        private List<String> distinctHeaders = new ArrayList<>();

        @Data
        public static class GroupSumRule {
            private String sumHeader;
            private String groupByHeader;
        }
    }


    public static void main(String[] args) {
        String filePath = "C:\\Users\\Lenovo\\Desktop\\新建文件夹 (3)\\中化信息2024年财务预算.xlsx";
        ExcelParseConfig config = new ExcelParseConfig();

        // 配置第一级分组规则
        config.getFirstLevelGroupHeaders().add("客户名称");
        config.getFirstLevelGroupHeaders().add("公司代码");

        // 配置分组求和规则
        ExcelParseConfig.GroupSumRule rule1 = new ExcelParseConfig.GroupSumRule();
        rule1.setSumHeader("余额（元）");
        rule1.setGroupByHeader("业务品种");
        config.getGroupSumRules().add(rule1);

        // 配置去重表头
        config.getDistinctHeaders().add("客户名称");

        try {
            Map<String, Object> result = ExcelParserUtil.parseExcel(filePath, config);
            System.out.println(result);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static Map<String, Object> parseExcel(String filePath, ExcelParseConfig config) throws IOException {
        Map<String, Object> result = new HashMap<>();
        List<List<Map<String, String>>> allSheetData = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            // 遍历每个Sheet
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                int headerRowIndex = 3;
                Map<String, Integer> headerIndexMap = getHeaderIndexMap(sheet.getRow(headerRowIndex));

                // 第一级分组，根据配置的表头组合隔离不同公司的数据
                Map<String, List<Map<String, String>>> firstLevelGroupedData = new HashMap<>();
                for (int rowIndex = headerRowIndex + 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    Map<String, String> rowData = new HashMap<>();
                    for (Map.Entry<String, Integer> entry : headerIndexMap.entrySet()) {
                        String header = entry.getKey();
                        int index = entry.getValue();
                        Cell cell = row.getCell(index);
                        if (cell != null) {
                            cell.setCellType(CellType.STRING);
                            rowData.put(header, cell.getStringCellValue());
                        } else {
                            rowData.put(header, "");
                        }
                    }
                    String firstLevelGroupKey = generateFirstLevelGroupKey(rowData, config.getFirstLevelGroupHeaders());
                    if (!firstLevelGroupedData.containsKey(firstLevelGroupKey)) {
                        firstLevelGroupedData.put(firstLevelGroupKey, new ArrayList<>());
                    }
                    firstLevelGroupedData.get(firstLevelGroupKey).add(rowData);
                }

                // 对每个第一级分组的数据进行进一步处理
                for (Map.Entry<String, List<Map<String, String>>> entry : firstLevelGroupedData.entrySet()) {
                    String groupKey = entry.getKey();
                    List<Map<String, String>> groupData = entry.getValue();

                    // 根据配置进行分组求和
// 根据配置进行分组求和
                    for (ExcelParseConfig.GroupSumRule rule : config.getGroupSumRules()) {
                        String sumHeader = rule.getSumHeader();
                        String groupByHeader = rule.getGroupByHeader();
                        Map<String, Double> sumMap = new HashMap<>();
                        for (Map<String, String> row : groupData) {
                            String subGroupKey = row.get(groupByHeader);
                            String sumValueStr = row.get(sumHeader);
                            log.info("sumValueStr:{}", sumValueStr + "subGroupKey " + subGroupKey);
//                            sumValueStr = sumValueStr.replace(",", "");
                            try {
                                double sumValue = Double.parseDouble(sumValueStr);
                                sumMap.put(subGroupKey, sumMap.getOrDefault(subGroupKey, 0.0) + sumValue);
                            } catch (NumberFormatException e) {
                                // 可以选择记录日志或者采取其他处理方式
                                System.err.println("无法将值 " + sumValueStr + " 转换为数字类型");
                            }
                        }
                        result.put(sumHeader + "_sumBy_" + groupByHeader + "_in_" + groupKey, sumMap);
                    }

                    // 根据配置进行去重
                    for (String distinctHeader : config.getDistinctHeaders()) {
                        List<String> distinctList = new ArrayList<>();
                        for (Map<String, String> row : groupData) {
                            String value = row.get(distinctHeader);
                            if (!distinctList.contains(value)) {
                                distinctList.add(value);
                            }
                        }
                        result.put(distinctHeader + "_distinct_in_" + groupKey, distinctList);
                    }
                }

                allSheetData.add(new ArrayList<>(firstLevelGroupedData.values()).stream().flatMap(List::stream).collect(Collectors.toList()));
            }
            result.put("allSheetData", allSheetData);
        }
        return result;
    }


    private static String generateFirstLevelGroupKey(Map<String, String> rowData, List<String> firstLevelGroupHeaders) {
        StringBuilder keyBuilder = new StringBuilder();
        for (String header : firstLevelGroupHeaders) {
            keyBuilder.append(rowData.get(header)).append("_");
        }
        return keyBuilder.toString().trim();
    }

    private static Map<String, Integer> getHeaderIndexMap(Row row) {
        Map<String, Integer> headerIndexMap = new HashMap<>();
        for (Cell cell : row) {
            if (cell != null) {
                cell.setCellType(CellType.STRING);
                headerIndexMap.put(cell.getStringCellValue(), cell.getColumnIndex());
            }
        }
        return headerIndexMap;
    }
}